<?php

require_once 'PlayerBalance.php';
require_once 'PlayerInformation.php';
require_once 'Betthreads.php';

class Player {

    private $playerID;
    private $username;
    private $email;
    private $password;
    private $oldpassword;
    private $salt;
    private $createDate;
    private $isDelete;
    private $maxMember;
    private $role;
    private $isLocked;
    private $parentID;
    private $levelID;
    private $playerBalance;
    private $playerInformation;
    private $betthreads;
    private $conn;

    function __construct() {
        $this->playerBalance = new PlayerBalance();
        $this->playerInformation = new PlayerInformation();
        $this->betthreads = new Betthreads();
    }

    public function getBetthreads() {
        return $this->betthreads;
    }

    public function setBetthreads($betthreads) {
        $this->betthreads = $betthreads;
    }

    public function getUsername() {
        return $this->username;
    }

    public function getEmail() {
        return $this->email;
    }

    public function getPassword() {
        return $this->password;
    }

    public function getSalt() {
        return $this->salt;
    }

    public function getCreateDate() {
        return $this->createDate;
    }

    public function getIsDelete() {
        return $this->isDelete;
    }

    public function getMaxMember() {
        return $this->maxMember;
    }

    public function getRole() {
        return $this->role;
    }

    public function getIsLocked() {
        return $this->isLocked;
    }

    public function getParentID() {
        return $this->parentID;
    }

    public function getLevelID() {
        return $this->levelID;
    }

    public function setUsername($username) {
        $this->username = $username;
    }

    public function setEmail($email) {
        $this->email = $email;
    }

    public function setPassword($password) {
        $this->password = $password;
    }

    public function setSalt($salt) {
        $this->salt = $salt;
    }

    public function setCreateDate($createDate) {
        $this->createDate = $createDate;
    }

    public function setIsDelete($isDelete) {
        $this->isDelete = $isDelete;
    }

    public function setMaxMember($maxMember) {
        $this->maxMember = $maxMember;
    }

    public function setRole($role) {
        $this->role = $role;
    }

    public function setIsLocked($isLocked) {
        $this->isLocked = $isLocked;
    }

    public function setParentID($parentID) {
        $this->parentID = $parentID;
    }

    public function setLevelID($levelID) {
        $this->levelID = $levelID;
    }

    public function getPlayerBalance() {
        return $this->playerBalance;
    }

    public function getPlayerInformation() {
        return $this->playerInformation;
    }

    public function getConn() {
        return $this->conn;
    }

    public function setPlayerBalance($playerBalance) {
        $this->playerBalance = $playerBalance;
    }

    public function setPlayerInformation($playerInformation) {
        $this->playerInformation = $playerInformation;
    }

    public function setConn($conn) {
        $this->conn = $conn;
    }

    public function getPlayerID() {
        return $this->playerID;
    }

    public function setPlayerID($playerID) {
        $this->playerID = $playerID;
    }

    public function getOldpassword() {
        return $this->oldpassword;
    }

    public function setOldpassword($oldpassword) {
        $this->oldpassword = $oldpassword;
    }

    function insertPlayer($conn) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strInsert = "INSERT INTO players(Username,Email,Password,Salt,CreatedDate,IsDelete,MaxMember,Role,IsLocked,ParentID,LevelID) "
                . "VALUES('$this->username','$this->email','$this->password','$this->salt',"
                . "'$this->createDate','$this->isDelete','$this->maxMember','$this->role','$this->isLocked','$this->parentID','$this->levelID')";
        $strQuery = mysqli_query($conn, $strInsert);
        $playerID = mysqli_insert_id($conn);
        return $playerID;
    }

    function getAllPlayer($conn, $start, $limit) {
        $start = $start - 1;
        $result = array();

        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT p.PlayerID,p.Username,p.Email,p.IsLocked,p.LastLogin,p.Role,pi.FirstName,pi.LastName,"
                . "pi.Phone,pi.Gender,(SELECT Username FROM players WHERE PlayerID = '$this->parentID') AS Parentname1,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentname2,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentname3,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentname4,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = '$this->parentID') AS Parentid1,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentid2,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentid3,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentid4,"
                . "(SELECT Role FROM players WHERE PlayerID = '$this->parentID') AS Parentrole1,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentrole2,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentrole3,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentrole4 "
                . "FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID "
                . "WHERE p.ParentID = '$this->parentID'" . " LIMIT $start,$limit";

        $strSelectAll = "SELECT count(p.PlayerID) As total "
                . "FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID "
                . "WHERE p.ParentID = '$this->parentID'";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $rowCountTotal = mysqli_fetch_array($queryCountTotal);
        $total = $rowCountTotal['total'];

        $strQuery = mysqli_query($conn, $strSelect);

        $strSelectParentInfor = "SELECT PlayerID,Username,Role,ParentID FROM players "
                . "WHERE PlayerID ='$this->parentID'";
        $strQueryParentInfor = mysqli_query($conn, $strSelectParentInfor);
        $row1 = mysqli_fetch_array($strQueryParentInfor);

        $arr = array();
        array_push($arr, $row1);
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function getAllSubPlayer($conn) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT p.PlayerID,p.Username,p.Email,p.IsLocked,p.LastLogin,p.Role,pi.FirstName,pi.LastName,pi.Phone,pi.Gender "
                . "FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID "
                . "WHERE p.ParentID = '$this->parentID'";
        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }
        return $arr;
    }

    function getAccountbalance($conn) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT p.Username,p.Email,pb.BMoney FROM players p JOIN playerinformation pi "
                . "ON p.PlayerID = pi.PlayerID JOIN playerbalance pb ON p.PlayerID = pb.PlayerID "
                . "WHERE p.PlayerID ='$this->playerID'";
        $strQuery = mysqli_query($conn, $strSelect);
        $row = mysqli_fetch_array($strQuery);
        return $row;
    }

    function changPassword($conn) {
        $result = -1;
        if ($this->checkOldPassword($conn) == 0)
            $result = -1;
        else {
            mysqli_query($conn, 'SET NAMES utf8');
            $saltEnc = $this->getSaltLogin($conn);
            $salt = base64_decode($saltEnc);
            $passEnc = md5($this->password . $saltEnc) . $salt;
            $strUpdate = "UPDATE players SET Password='$passEnc' WHERE PlayerID='$this->playerID'";
            $strQuery = mysqli_query($conn, $strUpdate);
            $result = $strQuery;
        }
        return $result;
    }

    function checkOldPassword($conn) {
        $passwordENC = md5($this->oldpassword . $this->getSaltLogin($conn)) . base64_decode($this->getSaltLogin($conn));
        $strSelect = "SELECT Password FROM players WHERE Password='$passwordENC' AND PlayerID = '$this->playerID'";
        $strQuery = mysqli_query($conn, $strSelect);
        $num = mysqli_num_rows($strQuery);
        if ($num > 0)
            return 1;
        else
            return 0;
    }

    function getSaltLogin($conn) {
        $select = "select Salt from players where PlayerID = '$this->playerID'";
        $query = mysqli_query($conn, $select);
        $row = mysqli_fetch_array($query);
        return $row["Salt"];
    }

    function loadAccountDetail($conn) {
        mysqli_query($conn, "SET NAMES utf8");
        $strSelect = "SELECT p.PlayerID,p.Username,p.Email,p.IsLocked,p.LastLogin,p.Role,p.ParentID,pi.FirstName,pi.LastName,pi.Phone,pi.Gender "
                . "FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID "
                . "WHERE p.PlayerID = '$this->playerID'";
        $strQuery = mysqli_query($conn, $strSelect);
        $row = mysqli_fetch_array($strQuery);
        return $row;
    }

    function updatePlayer($conn) {
        $lastname = $this->playerInformation->getLastname();
        $firstname = $this->playerInformation->getFirstname();
        $gender = $this->playerInformation->getGender();

        $saltEnc = $this->getSaltLogin($conn);
        $salt = base64_decode($saltEnc);
        $passEnc = md5($this->password . $saltEnc) . $salt;

        mysqli_query($conn, "SET NAMES utf8");
        $strUpdate = "UPDATE players p JOIN playerinformation pi "
                . "ON p.PlayerID = pi.PlayerID SET p.Username = '$this->username', p.Password = '$passEnc',"
                . "p.IsLocked = '$this->isLocked', pi.FirstName = '$firstname',pi.LastName='$lastname',pi.Gender = '$gender',"
                . " p.Email = '$this->email' WHERE p.PlayerID = '$this->playerID'";
        $strQuery = mysqli_query($conn, $strUpdate);
        return $strQuery;
    }

    function getAllPlayerBalance($conn, $start, $limit) {
        $result = array();
        $start = $start - 1;
        mysqli_query($conn, "SET NAMES utf8");
        $strSelect = "SELECT p.PlayerID,pb.BMoney,p.Username,p.LastLogin,p.Role,pi.FirstName,pi.LastName "
                . ",pi.Gender,(SELECT Username FROM players WHERE PlayerID = '$this->parentID') AS Parentname1,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentname2,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentname3,"
                . "(SELECT Username FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentname4,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = '$this->parentID') AS Parentid1,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentid2,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentid3,"
                . "(SELECT PlayerID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentid4,"
                . "(SELECT Role FROM players WHERE PlayerID = '$this->parentID') AS Parentrole1,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')) AS Parentrole2,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID'))) AS Parentrole3,"
                . "(SELECT Role FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = (SELECT ParentID FROM players WHERE PlayerID = '$this->parentID')))) AS Parentrole4"
                . " FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID JOIN playerbalance pb ON p.PlayerID = pb.PlayerID "
                . "WHERE p.ParentID = '$this->parentID'" . " LIMIT $start,$limit";

        $strSelectAll = "SELECT count(p.PlayerID) As total "
                . "FROM players p JOIN playerinformation pi ON p.PlayerID = pi.PlayerID "
                . "WHERE p.ParentID = '$this->parentID'";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $rowCountTotal = mysqli_fetch_array($queryCountTotal);
        $total = $rowCountTotal['total'];

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        $strSelectParentInfor = "SELECT PlayerID,Username,Role,ParentID FROM players "
                . "WHERE PlayerID ='$this->parentID'";
        $strQueryParentInfor = mysqli_query($conn, $strSelectParentInfor);
        $row1 = mysqli_fetch_array($strQueryParentInfor);

        array_push($arr, $row1);
        while ($row = mysqli_fetch_array($strQuery)) {
            $strSelect1 = "SELECT COUNT(PlayerID) as mems from players WHERE  ParentID =" . $row['PlayerID'];
            $strQuery1 = mysqli_query($conn, $strSelect1);
            $row1 = mysqli_fetch_array($strQuery1);
            $row['mems'] = $row1['mems'];
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function getMaxDateTime($conn) {
        mysqli_query($conn, "SET NAMES utf8");
        $strSelect = "select MAX(DATE_FORMAT(STR_TO_DATE(BetDate, '%d-%m-%Y'), '%Y-%m-%d')) AS MaxDate from betthreads";
        $strQuery = mysqli_query($conn, $strSelect);
        $row['MaxDate'] = mysqli_fetch_array($strQuery);
        return $row['MaxDate'];
    }

    function getTopUserWin($conn, $start, $limit) {
        $start = $start - 1;
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT pl.LastName,pl.FirstName,b.BetDate,SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Thang FROM provinces p "
                . "join betthreads b on p.ProvinceID = b.ProvinceID "
                . "join playerinformation pl on b.PlayerID = pl.PlayerID "
                . "join subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID  "
                . "WHERE b.WinTimes <> 0 and b.Pendding <> 0 "
                . "GROUP BY b.PlayerID ORDER BY Thang DESC " . "LIMIT $start,$limit";

        $strSelectAll = "SELECT count(pl.PlayerID) As total FROM provinces p "
                . "join betthreads b on p.ProvinceID = b.ProvinceID "
                . "join playerinformation pl on b.PlayerID = pl.PlayerID "
                . "join subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID  "
                . "WHERE b.WinTimes <> 0 and b.Pendding <> 0 "
                . "GROUP BY b.PlayerID";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $total = mysqli_num_rows($queryCountTotal);

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function getTopUserLose($conn, $start, $limit) {
        $start = $start - 1;
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT pl.LastName,pl.FirstName,b.BetDate,SUM(b.BetMoney) AS Thua FROM provinces p "
                . "join betthreads b on p.ProvinceID = b.ProvinceID "
                . "join playerinformation pl on b.PlayerID = pl.PlayerID "
                . "join subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . "WHERE b.WinTimes = 0 and b.Pendding <> 0 "
                . "GROUP BY b.PlayerID ORDER BY Thua DESC " . "LIMIT $start,$limit";

        $strSelectAll = "SELECT count(pl.PlayerID) As total FROM provinces p "
                . "join betthreads b on p.ProvinceID = b.ProvinceID "
                . "join playerinformation pl on b.PlayerID = pl.PlayerID "
                . "join subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID  "
                . "WHERE b.WinTimes = 0 and b.Pendding <> 0 "
                . "GROUP BY b.PlayerID";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $total = mysqli_num_rows($queryCountTotal);

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function getAllReportOfPlayer($conn, $start, $limit) {
        $start = $start - 1;
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT pl.LastName,pl.FirstName,r.`Name` AS Mien,h.`Name`"
                . " AS Loai,p.`Name` AS Tinh,b.BetNumber,b.BetMoney,"
                . "b.BetDate,b.WinTimes,b.Pendding,s.WinRateBet FROM provinces p "
                . "JOIN betthreads b on p.ProvinceID = b.ProvinceID "
                . "JOIN playerinformation pl on pl.PlayerID = b.PlayerID "
                . "JOIN players plr ON plr.PlayerID = b.PlayerID "
                . "JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . "JOIN regions r on s.RegionID = r.RegionID "
                . "JOIN subthreads h on h.SubThreadID = s.SubThreadID "
                . "WHERE if('$this->role' = 4,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))),"
                . "(if('$this->role'=3,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))),"
                . "(if('$this->role'=2,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')),"
                . "(if('$this->role'=1,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'),"
                . "''))))))) " . "LIMIT $start, $limit";

        $strSelectAll = "SELECT count(pl.FirstName) As total "
                . "FROM provinces p "
                . "JOIN betthreads b on p.ProvinceID = b.ProvinceID "
                . "JOIN playerinformation pl on pl.PlayerID = b.PlayerID "
                . "JOIN players plr ON plr.PlayerID = b.PlayerID "
                . "JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . "JOIN regions r on s.RegionID = r.RegionID "
                . "JOIN subthreads h on h.SubThreadID = s.SubThreadID "
                . "WHERE if('$this->role' = 4,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))),"
                . "(if('$this->role'=3,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))),"
                . "(if('$this->role'=2,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')),"
                . "(if('$this->role'=1,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'),"
                . "'')))))))";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $rowCountTotal = mysqli_fetch_array($queryCountTotal);
        $total = $rowCountTotal['total'];

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function showHistoryReport($conn, $start, $limit) {
        $start = $start - 1;
        mysqli_query($conn, 'SET NAMES utf8');
        $fromdate = $_GET['datefrom'];
        $todate = $_GET['dateto'];
        $strSelect = "SELECT pl.LastName,pl.FirstName,r.`Name` AS Mien,h.`Name`"
                . " AS Loai,p.`Name` AS Tinh,b.BetNumber,b.BetMoney,"
                . "b.BetDate,b.WinTimes,b.Pendding,s.WinRateBet FROM provinces p "
                . "JOIN betthreads b on p.ProvinceID = b.ProvinceID "
                . "JOIN playerinformation pl on pl.PlayerID = b.PlayerID "
                . "JOIN players plr ON plr.PlayerID = b.PlayerID "
                . "JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . "JOIN regions r on s.RegionID = r.RegionID "
                . "JOIN subthreads h on h.SubThreadID = s.SubThreadID "
                . "WHERE (STR_TO_DATE(b.BetDate,'%d-%m-%Y')  BETWEEN STR_TO_DATE('$fromdate', '%d-%m-%Y') and STR_TO_DATE('$todate', '%d-%m-%Y')) "
                . "AND if('$this->role' = 4,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))),"
                . "(if('$this->role'=3,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))),"
                . "(if('$this->role'=2,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')),"
                . "(if('$this->role'=1,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'),"
                . "'')))))))" . " LIMIT $start,$limit";

        $strSelectAll = "SELECT count(pl.FirstName) As total "
                . "FROM provinces p "
                . "JOIN betthreads b on p.ProvinceID = b.ProvinceID "
                . "JOIN playerinformation pl on pl.PlayerID = b.PlayerID "
                . "JOIN players plr ON plr.PlayerID = b.PlayerID "
                . "JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . "JOIN regions r on s.RegionID = r.RegionID "
                . "JOIN subthreads h on h.SubThreadID = s.SubThreadID "
                . "WHERE (STR_TO_DATE(b.BetDate,'%d-%m-%Y')  BETWEEN STR_TO_DATE('$fromdate', '%d-%m-%Y') and STR_TO_DATE('$todate', '%d-%m-%Y')) "
                . "AND if('$this->role' = 4,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))),"
                . "(if('$this->role'=3,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))),"
                . "(if('$this->role'=2,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID IN "
                . "(SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')),"
                . "(if('$this->role'=1,plr.PlayerID IN "
                . "(SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'),"
                . "'')))))))";
        $queryCountTotal = mysqli_query($conn, $strSelectAll);
        $rowCountTotal = mysqli_fetch_array($queryCountTotal);
        $total = $rowCountTotal['total'];

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }

        $result["Total"] = $total;
        $result["Data"] = $arr;

        return $result;
    }

    function getRevenusDayByDay($conn, $day) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT "
                . " '$day' AS Ngay, "
                . " COUNT(b.PlayerID) AS solanchoi, "
                . " SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Tongtienthang, "
                . " ( "
                . "  SUM(if(WinTimes=0,BetMoney,0)) "
                . " ) AS Tongtienththua, "
                . " SUM(if(WinTimes >0,1,0)) as Solanthang, "
                . " ( "
                . " COUNT(b.PlayerID) - SUM(if(WinTimes >0,1,0)) "
                . " ) AS Solanthua "
                . " from players plr JOIN betthreads b ON plr.PlayerID = b.PlayerID "
                . " JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . " WHERE b.BetDate = '$day' "
                . " AND if($this->role = 4,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN  "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))), "
                . " (if($this->role = 3,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))), "
                . " (if($this->role = 2,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')), "
                . " (if($this->role = 1,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'), "
                . " ''))))))) "
                . " GROUP BY MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y'))";

        $strQuery = mysqli_query($conn, $strSelect);
        $result = array();
        $arr = array();

        $noRow = mysqli_num_rows($strQuery);
        if ($noRow === 0) {
            $arr = NULL;
        } else {
            $row = mysqli_fetch_array($strQuery);
            $arr['Ngay'] = $row['Ngay'];
            $arr['Solanchoi'] = $row['solanchoi'];
            $arr['Tienthang'] = $row['Tongtienthang'];
            $arr['Tienthua'] = $row['Tongtienththua'];
            $arr['Solanthang'] = $row['Solanthang'];
            $arr['Solanthua'] = $row['Solanthua'];
        }
        $result["Ngay"] = $day;
        $result["Data"] = $arr;

        return $result;
    }

    function getRevenusDayByNoDay($conn, $noDay) {
        date_default_timezone_set("Asia/Ho_Chi_Minh");
        $day = new DateTime();
        $day->sub(new DateInterval('P' . $noDay . 'D'));
        $day = (string) $day->format('d-m-Y');

        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT "
                . " '$day' AS Ngay, "
                . " COUNT(b.PlayerID) AS solanchoi, "
                . " SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Tongtienthang, "
                . " ( "
                . "  SUM(if(WinTimes=0,BetMoney,0)) "
                . " ) AS Tongtienththua, "
                . " SUM(if(WinTimes >0,1,0)) as Solanthang, "
                . " ( "
                . " COUNT(b.PlayerID) - SUM(if(WinTimes >0,1,0)) "
                . " ) AS Solanthua "
                . " from players plr JOIN betthreads b ON plr.PlayerID = b.PlayerID "
                . " JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID "
                . " WHERE b.BetDate = '$day' "
                . " AND if($this->role = 4,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN  "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))), "
                . " (if($this->role = 3,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))), "
                . " (if($this->role = 2,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')), "
                . " (if($this->role = 1,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'), "
                . " ''))))))) "
                . " GROUP BY MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y'))";

        $strQuery = mysqli_query($conn, $strSelect);
        $result = array();
        $arr = array();

        $noRow = mysqli_num_rows($strQuery);
        if ($noRow === 0) {
            $arr = NULL;
        } else {
            $row = mysqli_fetch_array($strQuery);
            $arr['Ngay'] = $row['Ngay'];
            $arr['Solanchoi'] = $row['solanchoi'];
            $arr['Tienthang'] = $row['Tongtienthang'];
            $arr['Tienthua'] = $row['Tongtienththua'];
            $arr['Solanthang'] = $row['Solanthang'];
            $arr['Solanthua'] = $row['Solanthua'];
        }
        $result["Ngay"] = $day;
        $result["Data"] = $arr;

        return $result;
    }

    function getRevenusYearWithMonthByYear($conn, $year) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "select '$year' AS nam, "
                . " MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y')) AS thang, "
                . " COUNT(b.PlayerID) AS solanchoi, "
                . " SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Tongtienthang, "
                . " ( "
                . "  SUM(if(WinTimes=0,BetMoney,0)) "
                . "  ) AS Tongtienththua, "
                . " SUM(if(WinTimes >0,1,0)) as Solanthang, "
                . " ( "
                . "  COUNT(b.PlayerID) - SUM(if(WinTimes >0,1,0)) "
                . " ) AS Solanthua "
                . " from players plr JOIN betthreads b ON plr.PlayerID = b.PlayerID  "
                . " JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID  "
                . " WHERE (YEAR(STR_TO_DATE(b.BetDate,'%d-%m-%Y')) = '$year') "
                . " AND if($this->role = 4,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN  "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))), "
                . " (if($this->role = 3,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))), "
                . " (if($this->role = 2,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')), "
                . " (if($this->role = 1,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'), "
                . " ''))))))) "
                . " GROUP BY MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y'))";
        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        while ($row = mysqli_fetch_array($strQuery)) {
            array_push($arr, $row);
        }
        return $arr;
    }

    function getRevenusYearWithMonthByNoYear($conn, $noYear) {
        date_default_timezone_set("Asia/Ho_Chi_Minh");
        $year = new DateTime();
        $year->sub(new DateInterval('P' . $noYear . 'Y'));
        $year = (string) $year->format('Y');

        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "select '$year' AS Nam, "
                . " MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y')) AS Thang, "
                . " COUNT(b.PlayerID) AS Solanchoi, "
                . " SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Tongtienthang, "
                . " ( "
                . "  SUM(if(WinTimes=0,BetMoney,0)) "
                . "  ) AS Tongtienthua, "
                . " SUM(if(WinTimes >0,1,0)) as Solanthang, "
                . " ( "
                . "  COUNT(b.PlayerID) - SUM(if(WinTimes >0,1,0)) "
                . " ) AS Solanthua "
                . " from players plr JOIN betthreads b ON plr.PlayerID = b.PlayerID  "
                . " JOIN subthreadrule s on b.SubThreadRuleID = s.SubThreadRuleID  "
                . " WHERE (YEAR(STR_TO_DATE(b.BetDate,'%d-%m-%Y')) = '$year') "
                . " AND if($this->role = 4,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN  "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))), "
                . " (if($this->role = 3,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))), "
                . " (if($this->role = 2,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')), "
                . " (if($this->role = 1,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'), "
                . " ''))))))) "
                . " GROUP BY MONTH(STR_TO_DATE(BetDate,'%d-%m-%Y'))";
        $strQuery = mysqli_query($conn, $strSelect);
        $result = array();
        $arr = array();

        $noRow = mysqli_num_rows($strQuery);
        if ($noRow === 0) {
            $arr = NULL;
        } else {
            $arrThang = array();
            $j = 0;
            while ($row = mysqli_fetch_array($strQuery)) {
                $rowArray = array();
                $arrThang[$j] = $row['Thang'];
                $rowArray['Thang'] = $row['Thang'];
                $rowArray['Solanchoi'] = $row['Solanchoi'];
                $rowArray['Tongtienthang'] = $row['Tongtienthang'];
                $rowArray['Tongtienthua'] = $row['Tongtienthua'];
                $rowArray['Solanthang'] = $row['Solanthang'];
                $rowArray['Solanthua'] = $row['Solanthua'];
                array_push($arr, $rowArray);
                $j++;
            }
            $arrTam = array();
            for ($i = 0; $i < 12; $i++) {
                $arrTam[$i] = $i + 1;
                if (count($arr) < 12) {
                    $a = array();
                    $a['Thang'] = $i + 1;
                    $a['Solanchoi'] = 0;
                    $a['Tongtienthang'] = 0;
                    $a['Tongtienthua'] = 0;
                    $a['Solanthang'] = 0;
                    $a['Solanthua'] = 0;
                    array_push($arr, $a);
                }
            }
            $arrResult = array();
            $arrResult = array_unique(array_merge($arrThang, $arrTam));
            $arrResult1 = array();
            for ($k = 0; $k < 12; $k++) {
                $arrResult1[$k] = current($arrResult);
                next($arrResult);
                if ($arrResult1[$k] != $arr[$k]['Thang']) {
                    $rowArrayTmp = array();
                    if ($arrResult1[$k] < 10)
                        $arr[$k]['Thang'] = '0' . $arrResult1[$k];
                    else
                        $arr[$k]['Thang'] = '' . $arrResult1[$k];
                    $arr[$k]['Solanchoi'] = 0;
                    $arr[$k]['Tongtienthang'] = 0;
                    $arr[$k]['Tongtienthua'] = 0;
                    $arr[$k]['Solanthang'] = 0;
                    $arr[$k]['Solanthua'] = 0;
                }
                else {
                    if ($arrResult1[$k] < 10)
                        $arr[$k]['Thang'] = '0' . $arrResult1[$k];
                    else
                        $arr[$k]['Thang'] = '' . $arrResult1[$k];
                }
            }
            sort($arr);
        }

        $result["Nam"] = $year;
        $result["Data"] = $arr;

        return $result;
    }

    function getRevenusWeekByNoDay($conn, $number) {
        mysqli_query($conn, 'SET NAMES utf8');
        $strSelect = "SELECT "
                . " DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 7*$number DAY),'%d-%m-%Y') AS Ngaybatdau, "
                . " DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL (($number-1)*7 + 1) DAY),'%d-%m-%Y') AS Ngayketthuc, "
                . " BetDate AS Ngay, "
                . " COUNT(b.PlayerID) Solanchoi, "
                . " SUM(b.BetMoney*b.WinTimes*s.WinRateBet) AS Tongtienthang, "
                . " ( "
                . " SUM(if(WinTimes=0,BetMoney,0)) "
                . " ) AS Tongtienthua, "
                . " ( "
                . " SUM(if(WinTimes > 0,1,0)) "
                . " ) AS Solanthang, "
                . " ( "
                . " SUM(if(WinTimes = 0,1,0)) "
                . " ) AS Solanthua "
                . " FROM players plr JOIN betthreads b ON plr.PlayerID = b.PlayerID "
                . " JOIN subthreadrule s ON b.SubThreadRuleID = s.SubThreadRuleID "
                . " WHERE STR_TO_DATE(BetDate,'%d-%m-%Y') BETWEEN "
                . " STR_TO_DATE(DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 7*$number DAY),'%d-%m-%Y'),'%d-%m-%Y') "
                . " AND "
                . " STR_TO_DATE(DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL (1 + ($number-1)*7) DAY),'%d-%m-%Y'),'%d-%m-%Y') "
                . " AND if($this->role = 4,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN  "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID' )))), "
                . " (if($this->role = 3,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID = '$this->playerID'))), "
                . " (if($this->role = 2,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID IN "
                . " (SELECT PlayerID FROM players WHERE ParentID ='$this->playerID')), "
                . " (if($this->role = 1,plr.PlayerID IN "
                . " (SELECT PlayerID FROM players  WHERE ParentID ='$this->playerID'), "
                . " ''))))))) "
                . " GROUP BY BetDate";

        $strQuery = mysqli_query($conn, $strSelect);
        $arr = array();
        $arrNgay = array();
        $ngayCuoi;
        $j = 0;
        while ($row = mysqli_fetch_array($strQuery)) {
            $rowArray = array();
            $ngayCuoi = $row['Ngayketthuc'];
            $test = $row['Ngayketthuc'];

            $arrNgay[$j] = $row['Ngay'];

            $rowArray['Ngay'] = $row['Ngay'];
            $rowArray['Solanchoi'] = $row['Solanchoi'];
            $rowArray['Tongtienthang'] = $row['Tongtienthang'];
            $rowArray['Tongtienthua'] = $row['Tongtienthua'];
            $rowArray['Solanthang'] = $row['Solanthang'];
            $rowArray['Solanthua'] = $row['Solanthua'];
            array_push($arr, $rowArray);
            $j++;
        }
        //Xu ly khong co du lieu
        date_default_timezone_set("Asia/Ho_Chi_Minh");

        $date1 = new DateTime(date('d-m-Y'));
        $date1->sub(new DateInterval('P' . (($number - 1) * 7 + 1) . 'D'));
        $ngayCuoi = (string) $date1->format('d-m-Y');


        $arrTam = array();
        for ($i = 0; $i < 7; $i++) {
            $arrTam[$i] = $ngayCuoi;
            $date = new DateTime($ngayCuoi);
            $date->sub(new DateInterval('P1D'));
            $ngayCuoi = (string) $date->format('d-m-Y');

            //them phan tu vao $arr cho du 7 de sao sanh voi $arrResult
            if (count($arr) < 7) {
                $a = array();
                $a['Ngay'] = 0;
                $a['Solanchoi'] = 1;
                $a['Tongtienthang'] = 1;
                $a['Tongtienthua'] = 1;
                $a['Solanthang'] = 1;
                $a['Solanthua'] = 1;
                array_push($arr, $a);
            }
        }
        //union $arrNgay, $arrTam
        $arrResult = array_unique(array_merge($arrNgay, $arrTam));
        $arrResult1 = array();
        for ($k = 0; $k < 7; $k++) {
            $arrResult1[$k] = current($arrResult);
            next($arrResult);
            if ($arrResult1[$k] != $arr[$k]['Ngay']) {
                $rowArrayTmp = array();
                $arr[$k]['Ngay'] = $arrResult1[$k];
                $arr[$k]['Solanchoi'] = 0;
                $arr[$k]['Tongtienthang'] = 0;
                $arr[$k]['Tongtienthua'] = 0;
                $arr[$k]['Solanthang'] = 0;
                $arr[$k]['Solanthua'] = 0;
            }
        }

        $orderByDate = $my2 = array();
        foreach ($arr as $key => $row) {
            $orderByDate[$key] = strtotime($row["Ngay"]);
        }
        array_multisort($orderByDate, SORT_ASC, $arr);

        return $arr;
    }

}
